import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.Scanner;

public class sampleQuery {

	/**
	 * 두개의 날짜형(yyMMdd) String을 비교해서 일수의 차이를 계산
	 * 
	 */
	public static int fnc_DateExpired(String startDateString,
			String endDateString) {
		Date startDate = null;
		Date endDate = null;
		startDate = changeStringToDate(startDateString);
		endDate = changeStringToDate(endDateString);

		int tillDay = (int) (Math
				.abs((endDate.getTime() - startDate.getTime())) / 1000 / 60 / 60 / 24);

		return tillDay + 1;
	}

	/**
	 * 오늘 날짜 반환하는 util 함수
	 * 
	 */
	public static String getToday() {
		String dateFormat = "yyyyMMdd";
		Calendar cal = Calendar.getInstance();
		cal.setTime(new java.util.Date(System.currentTimeMillis()));
		return new java.text.SimpleDateFormat(dateFormat).format(cal.getTime());
	}

	/**
	 * Date형을 String으로 변환하는 util 함수
	 * 
	 */
	public static String changeDateToString(Date dateInput) {
		String dateFormat = "yyyyMMdd";
		String resultDate = null;
		SimpleDateFormat formatter = new SimpleDateFormat(dateFormat);
		resultDate = formatter.format(dateInput);
		return resultDate;
	}

	/**
	 * String형을 Date으로 변환하는 util 함수
	 * 
	 */
	public static Date changeStringToDate(String dateInput) {
		Date resultDate = null;
		String dateFormat = "yyyyMMdd";
		DateFormat df = new SimpleDateFormat(dateFormat);
		try {
			resultDate = df.parse(dateInput);
		} catch (ParseException e) {
			e.printStackTrace();
		}
		return resultDate;
	}


	public static void main(String[] args) {
		System.out.print("타입을 입력하세요(1 = 날짜별 통계, 2 = 시간별 통계, 3 = 지역별 통계) : ");
		Scanner sc = new Scanner(System.in);
		String type = sc.next();
		
		if ("1".equals(type))
			getDailyStats();
		else if ("2".equals(type))
			getHourlyStats();
		else if ("3".equals(type))
			getLocationStats();
		else {
			System.out.println("타입을 잘못 입력했습니다.");
			return;
		}
	}

	
	public static void getDailyStats() {
		String startDateString, endDateString;
		
		System.out.print("시작 날짜를 입력하세요 : ");
		Scanner sc = new Scanner(System.in);
		startDateString = sc.next();
		
		if(startDateString.length() != 8) {
			startDateString = getToday().substring(0, 6) + "01";
			endDateString = getToday().substring(0, 6) + String.valueOf( Integer.parseInt(getToday().substring(6, 8)) - 1 );
		}
		else {
			System.out.print("종료 날짜를 입력하세요 : ");
			sc = new Scanner(System.in);
			endDateString = sc.next();
		}

		System.out.print("타입을 입력하세요(1 = qrcode, 2 = nfc) : ");
		sc = new Scanner(System.in);
		String type = sc.next();
		String typeCast = "";
		if ("1".equals(type))
			typeCast = "qrcode";
		else if ("2".equals(type))
			typeCast = "data";
		else {
			System.out.println("타입을 잘못 입력했습니다.");
			return;
		}

		int day = fnc_DateExpired(startDateString, endDateString);

		// System.out.println("차이 : " + day);

		Date startDate = null;

		startDate = changeStringToDate(startDateString);

		Calendar dateCal = Calendar.getInstance();
		dateCal.setTime(startDate);

		String query = ";\nSELECT a.ts_name, a.cp_seq," + "\n";

		for (int i = 1; i < day + 1; i++) {
			String dateCalString = changeDateToString(dateCal.getTime());

			if (i != 1)
				query = query + "," + "\n";

			if ("qrcode".equals(typeCast)) {
				query = query
						+ "(SELECT NVL(sum(statscount),0) cnt\n"
						+ "FROM STATS_SERVICE_DAILY\n"
						+ "WHERE req_type = 'qrcode'\n"
						+ "AND tagcode IN (SELECT ic_urn FROM TAG_CODE_INFO WHERE cp_seq = a.cp_seq and ts_id=a.ts_id)\n" 
						+ "AND stamptime = '" + dateCalString + "'\n" 
						+ ") DATE_" + dateCalString;
			} else if ("data".equals(typeCast)) {
				query = query
						+ "(SELECT NVL(sum(statscount),0) cnt\n"
						+ "FROM STATS_SERVICE_DAILY\n"
						+ "WHERE (req_type != 'qrcode' AND req_type != 'template')\n"
						+ "AND tagcode IN (SELECT ic_urn FROM TAG_CODE_INFO WHERE cp_seq = a.cp_seq and ts_id=a.ts_id)\n" 
						+ "AND stamptime = '" + dateCalString + "'\n" 
						+ ") DATE_" + dateCalString;
			}

			dateCal.add(Calendar.DAY_OF_MONTH, 1);
		}

		query = query + "\n" + "FROM TAG_SERVICE_INFO a\n"
				+ "WHERE cp_seq = '0000000001'\n"
				+ "AND ts_id = '20110801031805_123'\n"
				+ "ORDER By a.ts_name";

		System.out.println(query);
	}

	public static void getHourlyStats() {
		String startDateString, endDateString;
		
		System.out.print("시작 날짜를 입력하세요 : ");
		Scanner sc = new Scanner(System.in);
		startDateString = sc.next();
		
		if(startDateString.length() != 8) {
			startDateString = getToday().substring(0, 6) + "01";
			endDateString = getToday().substring(0, 6) + String.valueOf( Integer.parseInt(getToday().substring(6, 8)) - 1 );
		}
		else {
			System.out.print("종료 날짜를 입력하세요 : ");
			sc = new Scanner(System.in);
			endDateString = sc.next();
		}
		
		int day = fnc_DateExpired(startDateString, endDateString);


		Date startDate = null;

		startDate = changeStringToDate(startDateString);

		Calendar dateCal = Calendar.getInstance();
		dateCal.setTime(startDate);

		//String query = ";\nSELECT sel_time, ";
		String query = ";\nSELECT ";
		for (int i = 1; i < day+1; i++) {
			String dateCalString = changeDateToString(dateCal.getTime());
			query = query 
					+ "QR_" + dateCalString + ", " 
					+ "NFC_" + dateCalString ;
			if (i < day) {
				query += ", ";
			}
			dateCal.add(Calendar.DAY_OF_MONTH, 1);
		}
		query += "\n";
		
		/*
		dateCal.setTime(startDate);
		query += "(";
		for (int i = 1; i < day+1; i++) {
			String dateCalString = changeDateToString(dateCal.getTime());
			query = query
					+ "QR_" + dateCalString + "+" 
					+ "NFC_" + dateCalString;
			if (i < day) {
				query += "+";
			}
			dateCal.add(Calendar.DAY_OF_MONTH, 1);			
		}
		query = query
				+ ") TOTAL\n";
		*/
		
		query = query
				+ "FROM (\n";
		
		for (int i = 0; i < 24; i++) {
			dateCal.setTime(startDate);
			String time = String.format("%02d", i);
			query += "SELECT '" + time + "' sel_time, \n";
			for (int j = 1; j < day + 1; j++) {
				String dateCalString = changeDateToString(dateCal.getTime());
	
				query = query
						+ "(SELECT NVL(sum(statscount),0) cnt\n"
						+ "FROM STATS_SERVICE_HOURLY\n"
						+ "WHERE req_type = 'qrcode'\n"
						+ "AND tagcode IN (SELECT ic_urn FROM TAG_CODE_INFO WHERE cp_seq = a.cp_seq and ts_id=a.ts_id)\n" 
						+ "AND stamptime = '" + dateCalString + time + "'\n" 
						+ ") QR_" + dateCalString;
				query += ",\n";
				
				query = query
						+ "(SELECT NVL(sum(statscount),0) cnt\n"
						+ "FROM STATS_SERVICE_HOURLY\n"
						+ "WHERE req_type != 'qrcode'\n"
						+ "AND tagcode IN (SELECT ic_urn FROM TAG_CODE_INFO WHERE cp_seq = a.cp_seq and ts_id=a.ts_id)\n"
						+ "AND stamptime = '" + dateCalString + time + "'\n"
						+ ") NFC_" + dateCalString;
				if (j < day) {
					query += ",\n";
				}
				else {
					query += "\n";
				}
				dateCal.add(Calendar.DAY_OF_MONTH, 1);
			}
			query = query
					+ "FROM tag_service_info a\n"
					+ "WHERE cp_seq = '0000000001' AND ts_id = '20110801031805_123'\n";	//상용 DB 경기도 버스
					//+ "WHERE cp_seq = '0000000001' AND ts_id = '20110705053437_66'\n";		//TB DB 경기도 버스
			if (i < 24 - 1) {
				query += "union\n";
			}
		}

		query = query + "\n" + ")";

		System.out.println(query);
	}
	
	public static void getLocationStats() {
		String startDateString, endDateString;
		
		System.out.print("시작 날짜를 입력하세요 : ");
		Scanner sc = new Scanner(System.in);
		startDateString = sc.next();
		
		if(startDateString.length() != 8) {
			startDateString = getToday().substring(0, 6) + "01";
			endDateString = getToday().substring(0, 6) + String.valueOf( Integer.parseInt(getToday().substring(6, 8)) - 1 );
		}
		else {
			System.out.print("종료 날짜를 입력하세요 : ");
			sc = new Scanner(System.in);
			endDateString = sc.next();
		}

		int day = fnc_DateExpired(startDateString, endDateString);

		// System.out.println("차이 : " + day);

		Date startDate = null;

		startDate = changeStringToDate(startDateString);

		Calendar dateCal = Calendar.getInstance();
		dateCal.setTime(startDate);

		String query = ";\nSELECT cs_name, ";
		for (int i = 1; i < day+1; i++) {
			String dateCalString = changeDateToString(dateCal.getTime());
			query = query 
					+ "QR_" + dateCalString + ", " 
					+ "NFC_" + dateCalString;
			if (i < day) {
				query += ", ";
			}
			dateCal.add(Calendar.DAY_OF_MONTH, 1);
		}
		query += "\n";
		
		dateCal.setTime(startDate);
		query += "(";
		for (int i = 1; i < day+1; i++) {
			String dateCalString = changeDateToString(dateCal.getTime());
			query = query
					+ "QR_" + dateCalString + "+" 
					+ "NFC_" + dateCalString;
			if (i < day) {
				query += "+";
			}
			dateCal.add(Calendar.DAY_OF_MONTH, 1);			
		}
		query = query
				+ ") TOTAL\n";
		
		query = query
				+ "FROM (\n"
				+ "SELECT a.CS_ID, NVL(STEP05_NAME, NVL(STEP04_NAME, NVL(STEP03_NAME, NVL(STEP02_NAME, NVL(STEP01_NAME, CS_NAME))))) CS_NAME, \n";
		
		dateCal.setTime(startDate);
		for (int j = 1; j < day + 1; j++) {
			String dateCalString = changeDateToString(dateCal.getTime());

			query = query
					+ "(SELECT NVL(sum(statscount),0) cnt\n"
					+ "FROM STATS_SERVICE_DAILY\n"
					+ "WHERE req_type = 'qrcode'\n"
					+ "AND tagcode IN (SELECT ic_urn FROM tag_code_info WHERE cs_id = a.cs_id AND ts_id = a.ts_id)\n" 
					+ "AND substr(STAMPTIME,0,8) = '" + dateCalString + "'\n" 
					+ ") QR_" + dateCalString;
			query += ",\n";
			
			query = query
					+ "(SELECT NVL(sum(statscount),0) cnt\n"
					+ "FROM STATS_SERVICE_DAILY\n"
					+ "WHERE req_type != 'qrcode'\n"
					+ "AND tagcode IN (SELECT ic_urn FROM tag_code_info WHERE cs_id = a.cs_id AND ts_id = a.ts_id)\n" 
					+ "AND substr(STAMPTIME,0,8) = '" + dateCalString + "'\n" 
					+ ") NFC_" + dateCalString;
			if (j < day) {
				query += ",\n";
			}
			else {
				query += "\n";
			}
			dateCal.add(Calendar.DAY_OF_MONTH, 1);
		}
		query = query
				+ "	FROM tag_common_sort_service_info a\n"
				+ "WHERE a.step01_value >=1 AND ts_id = '20110801031805_123' AND cs_group = '20110810045935_41' \n"
				+ "ORDER BY step01_name, NVL(step02_name,'_'), NVL(step03_name,'_'), NVL(step04_name,'_'), NVL(step05_name,'_')\n";
		
		query = query + "\n" + ")";

		System.out.println(query);
	}
	
}
